# coding=utf-8

# import pyodbc
import pymssql

# 站名，服务器，用户名，密码
# 超时连接58003，80004
sqlserver={
'50001':('50001','20.0.5.10','sa',''),
'50003':('50003','20.0.8.11','sa','123456'),
'50004':('50004','20.0.15.10','sa','123456'),
'50005':('50005','20.0.2.10','sa','123456'),
'50006':('50006','20.0.10.10','sa','123456'),
'50007':('50007','20.0.12.10','sa','123456'),
'50008':('50008','20.0.13.10','sa',''),
'50009':('50009','20.0.7.10','sa','123456'),
'50010':('50010','20.0.16.10','sa','123456'),
'50011':('50011','20.0.17.10','sa',''),
'50012':('50012','20.0.18.10','sa','123456'),
'50013':('50013','20.0.19.10','sa','123456'),
'50014':('50014','20.0.20.10','sa','123456'),
'50016':('50016','20.0.22.10','sa','123456'),
'50017':('50017','20.0.23.10','sa','123456'),
'50018':('50018','20.0.24.10','sa',''),
'50019':('50019','20.0.26.10','sa',''),
'50020':('50020','20.0.29.10','sa','123456'),
'50021':('50021','20.0.30.10','sa','123456'),
'50022':('50022','20.0.14.10','sa',''),
'50024':('50024','20.0.25.10','sa','123456'),
'50025':('50025','20.0.11.10','sa','123456'),
'58001':('58001','20.0.65.10','sa','123456'),
'58002':('58002','20.0.66.10','sa','123456'),
# '58003':('58003','20.0.67.10','sa','123456'),
'58004':('58004','20.0.68.10','sa','123456'),
'58005':('58005','20.0.69.10','sa','123456'),
'58006':('58006','20.0.70.10','sa','123456'),
'58007':('58007','20.0.72.10','sa','123456'),
'58012':('58012','20.0.77.10','sa','123456'),
'58015':('58015','20.0.80.10','sa','123456'),
'58016':('58016','20.0.81.10','sa','123456'),
'80001':('80001','20.0.31.10','sa','123456'),
'80002':('80002','20.0.32.10','sa','123456'),
'80003':('80003','20.0.33.10','sa','123456'),
# '80004':('80004','20.10.34.10','sa','123456')
}









class ODBC_MS:
    ''''' 对pyodbc库的操作进行简单封装
    pyodbc库的下载地址:http://code.google.com/p/pyodbc/downloads/list
    使用该库时，需要在Sql Server Configuration Manager里面将TCP/IP协议开启
    此类完成对数据库DB的连接/查询/执行操作
    正确的连接方式如下:
    cnxn = pyodbc.connect('DRIVER={SQL SERVER};SERVER=ZHANGHUAMIN\MSSQLSERVER_ZHM;DATABASE=AdventureWorks2008;UID=sa;PWD=wa1234')
    cnxn = pyodbc.connect(DRIVER='{SQL SERVER}',SERVER=r'ZHANGHUAMIN\MSSQLSERVER_ZHM',DATABASE='AdventureWorks2008',UID='sa',PWD='wa1234',charset="utf-8")
    '''

    def __init__(self, DRIVER, SERVER, DATABASE, UID, PWD):
        ''''' initialization '''
        self.DRIVER = DRIVER
        self.SERVER = SERVER
        self.DATABASE = DATABASE
        self.UID = UID
        self.PWD = PWD

    def __GetConnect(self):
        ''''' Connect to the DB '''
        if not self.DATABASE:
            raise (NameError, "no setting db info")
        # conn=pymssql.connect(host='20.0.5.10',user='sa',password='',database='enablerdb')
        self.conn = pymssql.connect( host=self.SERVER, database=self.DATABASE,
                                     password=self.PWD,user=self.UID)
        # self.conn = pyodbc.connect(DRIVER=self.DRIVER, SERVER=self.SERVER, DATABASE=self.DATABASE, UID=self.UID, PWD=self.PWD)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "connected failed!")
        else:
            return cur

    def ExecQuery(self, sql):
        ''''' Perform one Sql statement '''
        cur = self.__GetConnect()  # 建立链接并创建数据库操作指针
        cur.execute(sql)  # 通过指针来执行sql指令
        ret = cur.fetchall()  # 通过指针来获取sql指令响应数据
        cur.close()  # 游标指标关闭
        self.conn.close()  # 关闭数据库连接
        return ret

    def ExecNoQuery(self, sql):
        ''''' Person one Sql statement like write data, or create table, database and so on'''
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()  # 连接句柄来提交
        cur.close()
        self.conn.close()


def main():
    for s in sqlserver.keys():
        # sqlserver.get(s)
        print s
        ms = ODBC_MS('{SQL SERVER}', sqlserver.get(s)[1], 'enablerdb', sqlserver.get(s)[2],
                     sqlserver.get(s)[3])  # zhm_db数据库是在sql server 终端里先创建好的
        # print ms.ExecQuery("select * from dbo.transactions where transaction_date>'2019-03-08 10:00'")

    # ms = ODBC_MS('{SQL SERVER}', '20.10.34.10', 'enablerdb', 'sa',  '')  # zhm_db数据库是在sql server 终端里先创建好的
    # print ms.ExecQuery("select * from dbo.transactions where transaction_date>'2019-03-08'")

    # print ms.ExecQuery("select * from dbo.transactions where transaction_date>'2019-03-08'")


if __name__ == '__main__':
    main()